扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条
包定义：
| 以下是引用片段： create or replace package MaterialManage is TYPE T_CURSOR IS REF CURSOR; Procedure Per_QuickPage ( TbName in varchar2, --表名 FieldStr in varchar2, --字段集 RowFilter in varchar2, --过滤条件 SortStr in varchar2, --排序集 RownumFieldStr in varchar2, --分页条件 TotalCount out number, --总记录数 Cur_ReturnCur out T_CURSOR --返回的游标 ); end MaterialManage; |
包主体：
| 以下是引用片段： create or replace package body MaterialManage is Procedure Per_QuickPage ( TbName in varchar2, --表 名 FieldStr in varchar2, --字段集 RowFilter in varchar2, --过滤条件 SortStr in varchar2, --排序集 MinRowNum in number, --分页小值 MaxRowNum in number, --分页大值 TotalCount out number, --总记录数 Cur_ReturnCur out T_CURSOR ) is v_SourceTb1 varchar2(3000); --动态表名1 v_SourceTb2 varchar2(3000); --动态表名2 v_SourceTb3 varchar2(3000); --动态表名3 v_SourceTb4 varchar2(3000); --动态表名4 v_TotalCount varchar2(50); --总记录数 v_sql varchar2(3000); --动态sql begin v_SourceTb1 := ’’’’(select ’’’’|| FieldStr ||’’’’ from ’’’’|| TbName ||’’’’) SourceTb1’’’’; v_SourceTb2 := ’’’’(select * from ’’’’|| v_SourceTb1 ||’’’’ where ’’’’|| RowFilter ||’’’’ ’’’’|| SortStr ||’’’’) SourceTb2’’’’; v_SourceTb3 := ’’’’(select rownum as Rowindex,SourceTb2.* from ’’’’|| v_SourceTb2 ||’’’’ where rownum<=’’’’|| MaxRowNum ||’’’’) SourceTb3’’’’; v_SourceTb4 := ’’’’(select * from ’’’’|| v_SourceTb1 ||’’’’ where ’’’’|| RowFilter ||’’’’) SourceTb4’’’’; v_sql := ’’’’select count(*) as TotalCount from ’’’’|| v_SourceTb4; execute immediate v_sql into v_TotalCount; TotalCount := v_TotalCount; v_sql := ’’’’select * from ’’’’|| v_SourceTb3 ||’’’’ where RowIndex >=’’’’||MinRowNum; open Cur_ReturnCur for v_sql; end Per_QuickPage; END MaterialManage; |
由于
存储过程返回了两个参数：TotalCount ：当前条件下的总记录数 Cur_ReturnCur ：游标类型，就是所要读取的记录的集合
下面是ASP.NET中调用的代码：
| 以下是引用片段： { this.style.display=’none’; Codehighlighter1_2319_2685_Open_Text.style.display=’none’; Codehighlighter1_2319_2685_Closed_Image.style.display=’inline’; Codehighlighter1_2319_2685_Closed_Text.style.display=’inline’; }" src="http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif" align="top" />{ this.style.display=’none’; Codehighlighter1_2319_2685_Closed_Text.style.display=’none’; Codehighlighter1_2319_2685_Open_Image.style.display=’inline’; Codehighlighter1_2319_2685_Open_Text.style.display=’inline’; }" src="http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif" align="top" />/**//// <summary> /// 调用存储过程实现快速分页 /// </summary> /// <param name="TbName">表名称</param> /// <param name="FieldStr">字段名称</param> /// <param name="RowFilter">过滤条件</param> /// <param name="SortStr">排序字段</param> /// <param name="MinPageNum">分页小值</param> /// <param name="MaxPageNum">分页大值</param> /// <param name="TotalCount">总记录(需要返回)</param> /// <returns>DataTable</returns> public DataTable QuickPage(string TbName,string FieldStr,string RowFilter,string SortStr,int MinRowNum,int MaxRowNum,ref int RecordCount) { this.style.display=’none’; Codehighlighter1_2824_4736_Open_Text.style.display=’none’; Codehighlighter1_2824_4736_Closed_Image.style.display=’inline’; Codehighlighter1_2824_4736_Closed_Text.style.display=’inline’; }" src="http://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif" align="top" />{ this.style.display=’none’; Codehighlighter1_2824_4736_Closed_Text.style.display=’none’; Codehighlighter1_2824_4736_Open_Image.style.display=’inline’; Codehighlighter1_2824_4736_Open_Text.style.display=’inline’; }" src="http://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif" align="top" />{ OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["OracleConnstr"].ToString()); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "MaterialManage.Per_QuickPage"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("TbName",OracleType.VarChar,50); //表 名 cmd.Parameters["TbName"].Direction = ParameterDirection.Input; cmd.Parameters["TbName"].Value = TbName; cmd.Parameters.Add("FieldStr",OracleType.VarChar,3000); //字段集 cmd.Parameters["FieldStr"].Direction = ParameterDirection.Input; cmd.Parameters["FieldStr"].Value = FieldStr; cmd.Parameters.Add("RowFilter",OracleType.VarChar,3000); //过滤条件 cmd.Parameters["RowFilter"].Direction = ParameterDirection.Input; cmd.Parameters["RowFilter"].Value = RowFilter; cmd.Parameters.Add("SortStr",OracleType.VarChar,3000); //排序字段 cmd.Parameters["SortStr"].Direction = ParameterDirection.Input; cmd.Parameters["SortStr"].Value = SortStr; cmd.Parameters.Add("MinRowNum",OracleType.Number); //分页小值 cmd.Parameters["MinRowNum"].Direction = ParameterDirection.Input; cmd.Parameters["MinRowNum"].Value = MinRowNum; cmd.Parameters.Add("MaxRowNum",OracleType.Number); //分页大值 cmd.Parameters["MaxRowNum"].Direction = ParameterDirection.Input; cmd.Parameters["MaxRowNum"].Value = MaxRowNum; cmd.Parameters.Add("TotalCount",OracleType.Number); //页总记录数 cmd.Parameters["TotalCount"].Direction = ParameterDirection.Output; cmd.Parameters["TotalCount"].Value = 0; cmd.Parameters.Add("Cur_ReturnCur",OracleType.Cursor); //返回的游标 cmd.Parameters["Cur_ReturnCur"].Direction = ParameterDirection.Output; DataSet Ds = new DataSet(); OracleDataAdapter adapter= new OracleDataAdapter(cmd); adapter.Fill(Ds); conn.Close(); //总记录数 RecordCount = int.Parse(cmd.Parameters["TotalCount"].Value.ToString()); return Ds.Tables[0]; } |
濠电姷顣介埀顒€鍟块埀顒€缍婇幃妯诲緞閹邦剛鐣洪梺闈浥堥弲婊勬叏濠婂牊鍋ㄦい鏍ㄧ〒閹藉啴鏌熼悜鈺傛珚鐎规洘宀稿畷鍫曞煛閸屾粍娈搁梻浣筋嚃閸ㄤ即宕㈤弽顐ュС闁挎稑瀚崰鍡樸亜閵堝懎濮┑鈽嗗亝濠㈡ê螞濡ゅ懏鍋傛繛鍡樻尭鐎氬鏌嶈閸撶喎顕ｉ渚婄矗濞撴埃鍋撻柣娑欐崌閺屾稑鈹戦崨顕呮▊缂備焦顨呴惌鍌炵嵁鎼淬劌鐒垫い鎺戝鐎氬銇勯弽銊ф噥缂佽妫濋弻鐔碱敇瑜嶉悘鑼磼鏉堛劎绠為柡灞芥喘閺佹劙宕熼鐘虫緰闂佽崵濮抽梽宥夊垂閽樺）锝夊礋椤栨稑娈滈梺纭呮硾椤洟鍩€椤掆偓閿曪妇妲愰弮鍫濈闁绘劕寮Δ鍛厸闁割偒鍋勯悘锕傛煕鐎ｎ偆澧紒鍌涘笧閹瑰嫰鎼圭憴鍕靛晥闂備礁鎼€氱兘宕归柆宥呯；鐎广儱顦伴崕宥夋煕閺囥劌澧ù鐘趁湁闁挎繂妫楅埢鏇㈡煃瑜滈崜姘跺蓟閵娧勵偨闁绘劕顕埢鏇㈡倵閿濆倹娅囨い蹇涗憾閺屾洟宕遍鐔奉伓
京公网安备 11010802021500号